package com.wangzhou.users.dao.implay;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.wangzhou.common.util.DbUtil;
import com.wangzhou.common.util.PageInfo;
import com.wangzhou.users.dao.IAAdminsDAO;
import com.wangzhou.users.pojo.AAdmins;

/**
 * 管理员表的DAO实现类
 * @author zjx
 *
 */
public class AAdminsDAOImplay implements IAAdminsDAO
{
	/**
	 * 将数据库里面的记录变成pojo(java对象)
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private AAdmins conver(ResultSet rs) throws SQLException
	{
		AAdmins admins = new AAdmins();
		admins.setId(rs.getInt("id"));
		admins.setEmail(rs.getString("email"));
		admins.setPassword(rs.getString("password"));
		admins.setTrueName(rs.getString("trueName"));
		admins.setPhone(rs.getString("phone"));
		admins.setQq(rs.getString("qq"));
		admins.setStatus(rs.getByte("status"));
		admins.setLoginCount(rs.getInt("loginCount"));
		admins.setCreatetime(rs.getTimestamp("createtime"));
		admins.setUpdatetime(rs.getTimestamp("updatetime"));
		admins.setLasttime(rs.getTimestamp("lasttime"));
		return admins;
	}
	
	@Override
	public int insert(AAdmins admins)
	{
		Connection conn = null ; 
		PreparedStatement pst = null ; 
		ResultSet rs = null ; 
		/*
		 * insert into `a_admins` (`email`, `password`, `truename`, `phone`, `qq`, `status`, `logincount`, `createtime`, `updatetime`, 
		 * `lasttime`) values ('test@sian.com', '1111', '222', '111', '1', '1', '11', '2016-05-14 10:27:30', 
		 * '2016-05-14 10:27:32', '2016-05-14 10:27:35')
		 */
		String sql = "insert into `a_admins` (`email`, `password`, `trueName`, `phone`, `qq`, `status`, "
				+ "`logincount`, `createtime`, `updatetime`,`lasttime`) values "
				+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ; 
		try
		{
			//获得链接
			conn = DbUtil.INSTANCE().getConnection();
			pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pst.setObject(1, admins.getEmail());
			pst.setObject(2, admins.getPassword());
			pst.setObject(3, admins.getTrueName());
			pst.setObject(4, admins.getPhone());
			pst.setObject(5, admins.getQq());
			pst.setObject(6, admins.getStatus());
			pst.setObject(7, admins.getLoginCount());
			pst.setObject(8, admins.getCreatetime());
			pst.setObject(9, admins.getUpdatetime());
			pst.setObject(10, admins.getLasttime());
			
			int res = pst.executeUpdate();
			System.out.println(this.getClass() + "--插入一条记录,影响条数:" + res);
			rs = pst.getGeneratedKeys();
			int id = 0 ; 
			if(rs.next())
			{
				id = rs.getInt(1);
				admins.setId(id);
			}
			return id ; 
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			DbUtil.INSTANCE().closeAll(conn, pst, rs);
		}
		return 0;
	}

	@Override
	public int update(String type,AAdmins admins)
	{
		if("1".equalsIgnoreCase(type))
		{
			//按照id全部更新
			/*
			 * update `a_admins` SET `password`='222' where (`id`='6') LIMIT 1
			 */
			String sql = "update `a_admins` set email = ?,`password`=?,trueName= ?,phone = ?,qq = ?,"
					+ "status = ? , loginCount = ? , updatetime = ? , lasttime = ? "
					+ " where `id`= ? " ; 
			int res = 0;
			Connection conn = null ;
			PreparedStatement pst = null ;
			try
			{
				conn = DbUtil.INSTANCE().getConnection();
				pst = conn.prepareStatement(sql);
				pst.setObject(1, admins.getEmail());
				pst.setObject(2, admins.getPassword());
				pst.setObject(3, admins.getTrueName());
				pst.setObject(4, admins.getPhone());
				pst.setObject(5, admins.getQq());
				pst.setObject(6, admins.getStatus());
				pst.setObject(7, admins.getLoginCount());
				pst.setObject(8, admins.getUpdatetime());
				pst.setObject(9, admins.getLasttime());
				pst.setObject(10, admins.getId());
				res = pst.executeUpdate();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, null);
			}
			return res ; 
		}
		return 0;
	}

	@Override
	public int delete(String type,AAdmins admins)
	{
		return 0;
	}

	@Override
	public AAdmins findOne(String type, Map<String, Object> paramsMap)
	{
		if("1".equalsIgnoreCase(type))
		{
			//按照id查询
			Connection conn = null ; 
			PreparedStatement pst = null ; 
			ResultSet rs = null ; 
			String sql = "select * from a_admins where id = ? " ; 
			try
			{
				//获得链接
				conn = DbUtil.INSTANCE().getConnection();
				pst = conn.prepareStatement(sql);
				pst.setObject(1, paramsMap.get("id") + "");
				
				rs = pst.executeQuery();
				//结果集肯定只有一条
				if(rs.next())
				{
					AAdmins admins = conver(rs);
					return admins ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, rs);
			}
		}else if("2".equalsIgnoreCase(type))
		{
			//按照email查询
			Connection conn = null ; 
			PreparedStatement pst = null ; 
			ResultSet rs = null ; 
			String sql = "select * from a_admins where email = ? " ; 
			try
			{
				//获得链接
				conn = DbUtil.INSTANCE().getConnection();
				pst = conn.prepareStatement(sql);
				pst.setObject(1, paramsMap.get("email") + "");
				
				rs = pst.executeQuery();
				//结果集肯定只有一条
				if(rs.next())
				{
					AAdmins admins = conver(rs);
					return admins ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, rs);
			}
		}
		return null ; 
	}

	@Override
	public List<AAdmins> findCondList(PageInfo pageInfo, String type, Map<String, Object> paramsMap)
	{
		List<AAdmins> adminsList = new ArrayList<AAdmins>();
		if("1".equalsIgnoreCase(type))
		{
			//按照id查询
			Connection conn = null ; 
			PreparedStatement pst = null ; 
			ResultSet rs = null ; 
			StringBuffer sqlsb = new StringBuffer();
			sqlsb.append("select * from a_admins ad where 1 = 1 ") ; 
			try
			{
				//获得链接
				conn = DbUtil.INSTANCE().getConnection();
				
				List paramList = new ArrayList();
				if(!"".equalsIgnoreCase(paramsMap.get("keyword") + ""))
				{
					//关键字查询
					sqlsb.append(" and (ad.email like ? or ad.trueName like ? or ad.phone like ? or ad.qq like ? )");
					paramList.add("%" + paramsMap.get("keyword") + "%");
				}
				if(!"".equalsIgnoreCase(paramsMap.get("status") + ""))
				{
					//按照状态查询
					sqlsb.append(" and ad.status = ? ");
					paramList.add(paramsMap.get("status"));
				}
				
				if(paramsMap.get("stDate") instanceof Date && paramsMap.get("edDate") instanceof Date)
				{
					//按照时间范围查询
					sqlsb.append(" and ad.createtime >= ? and ad.createtime <= ? ");
					paramList.add(paramsMap.get("stDate"));
					paramList.add(paramsMap.get("edDate"));
				}
				
				//计算总条数
				/* 目的:
				 * 语句:select * from a_admins where 1 = 1 and status = 1; 
				 * 变成:select count(*) from a_admin where 1 = 1 and status = 1;
				 */
				String sqlIndex = "select *" ;
				String sqlCount = "select count(*) " + sqlsb.substring(sqlsb.indexOf(sqlIndex) + sqlIndex.length(), sqlsb.length());
				pst = conn.prepareStatement(sqlCount);
				int count = 1 ; 
				for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
				{
					Object obj = (Object) iterator.next();
					pst.setObject(count, obj);
					count ++ ; 
				}
				rs = pst.executeQuery();
				System.out.println("总条数的sql:" + sqlCount);
				//结果肯定有1条
				if(rs.next())
				{
					//设置总条数
					pageInfo.setTotalRecord(rs.getInt(1));
				}
				pst.close();
				rs.close();
				
				if(pageInfo.getTotalRecord() > 0)
				{
					//数据库设置条数
					sqlsb.append(" limit " + pageInfo.getCurrentRecord() + "," + pageInfo.getPageSize());
					System.out.println("分页的sql:" + sqlsb.toString());
					pst = conn.prepareStatement(sqlsb.toString());
					//pst.setObject(1, paramsMap.get("id") + "");
					//设置条件的值
					count = 1 ; 
					for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
					{
						Object obj = (Object) iterator.next();
						pst.setObject(count, obj);
						count ++ ; 
					}
					rs = pst.executeQuery();
					//循环结果集
					while(rs.next())
					{
						AAdmins admins = conver(rs);
						adminsList.add(admins);
					}
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, rs);
			}
		}
		return adminsList ; 
	}
	
}
